package com.kwan80gmail.flextime.models;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Locale;


/**
 * Created by Kevin on 2015-03-10.
 */
public class SqlLiteHelper extends SQLiteOpenHelper
{
    public SqlLiteHelper(Context context)
    {
        super(context, "places.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String createTablePlace = "CREATE TABLE place (placeId INTEGER PRIMARY KEY, name TEXT, latitude REAL , longitude REAL, radius INTEGER)";
        db.execSQL(createTablePlace);

        String CreateTablePlaceData = "CREATE TABLE session (session_date NUMERIC  PRIMARY KEY, enter INTEGER," +
                "exit INTEGER, duration INTEGER, placeId_fk INTEGER, FOREIGN KEY(placeId_fk) REFERENCES place(placeId))";


        db.execSQL(CreateTablePlaceData);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        String query = "DROP TABLE IF EXISTS place";
        db.execSQL(query);
        String query2 = "DROP TABLE IF EXISTS session";
        db.execSQL(query2);

        onCreate(db);
    }

    public void insertPlace(Place place)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put("placeId", place.getlocationId());
        values.put("name", place.getName());
        values.put("latitude", place.getLatitude());
        values.put("longitude", place.getLongitude());
        values.put("radius", place.getRadius());

        db.insert("place", null, values);

        db.close();

    }

    public void insertSession(Session session)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put("session_date", String.valueOf(session.getSession_date()));
        values.put("start", String.valueOf(session.getEnter()));
        values.put("stop", String.valueOf(session.getExit()));
        values.put("duration", String.valueOf(session.getDuration()));
        values.put("placeId_fk", String.valueOf(session.getPlace_id_FK()));

        db.insert("session", null, values);

        db.close();
    }

    public int updatePlace(Place place)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put("placeId", place.getlocationId());
        values.put("name", place.getName());
        values.put("latitude", place.getLatitude());
        values.put("longitude", place.getLongitude());
        values.put("radius", place.getRadius());

        return db.update("place", values, "placeId" + "= ?", new String[]{String.valueOf(place.getlocationId())});
    }

    public int updatePlaceName(String name, int locationId)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();


        values.put("name", name);

        return db.update("place", values, "placeId" + "= ?", new String[]{String.valueOf(locationId)});
    }

    public int updateSession(Place_Data place_data)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();

        values.put("session_date", String.valueOf(place_data.getSession_date()));
        values.put("start", String.valueOf(place_data.getSession_start()));
        values.put("stop", String.valueOf(place_data.getSession_end()));
        values.put("duration", String.valueOf(place_data.getDuration()));
        values.put("placeId_fk", String.valueOf(place_data.getLocationId_fk()));

        return db.update("place_data", values, "placeId_fk" + "= ?", new String[]{String.valueOf(place_data.getLocationId_fk())});
    }

    public void deletePlaceByName(int id)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        String deleteLocationQuery = "DELETE FROM place where placeId= '" + id + "'";

        db.execSQL(deleteLocationQuery);
    }

    public ArrayList<Place> getPlaces()
    {
        ArrayList<Place> places = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();

        String getLocationsQuery = "SELECT * FROM place";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);

        if (cursor.moveToFirst())
        {
            do
            {
                {
                    Place place = new Place(cursor.getInt(0), cursor.getString(1), cursor.getDouble(2), cursor.getDouble(3), cursor.getInt(4));
                    places.add(place);
                }
            } while (cursor.moveToNext());
        }

        return places;
    }

    public Place getPlaceById(int id)
    {
        ArrayList<Place> places = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Place place;

        String getLocationsQuery = "SELECT * FROM place WHERE placeId = '" + id + "'";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);


        if (cursor.moveToFirst())
        {
            do
            {
                {
                    place = new Place(cursor.getInt(0), cursor.getString(1), cursor.getDouble(2), cursor.getDouble(3), cursor.getInt(4));
                    places.add(place);
                    return place;
                }
            } while (cursor.moveToNext());
        }


        return null;
    }

    public Place getPlaceByName(String name)
    {
        ArrayList<Place> places = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Place place;

        String getLocationsQuery = "SELECT * FROM place WHERE name = '" + name + "'";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);


        if (cursor.moveToFirst())
        {
            do
            {
                {
                    place = new Place(cursor.getInt(0), cursor.getString(1), cursor.getDouble(2), cursor.getDouble(3), cursor.getInt(4));
                    places.add(place);
                    return place;
                }
            } while (cursor.moveToNext());
        }


        return null;
    }

    public int getPlaceIdByName(String name)
    {
        ArrayList<Place> places = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Place place;

        String getLocationsQuery = "SELECT * FROM place WHERE name = '" + name + "'";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);


        if (cursor.moveToFirst())
        {
            do
            {
                {
                    place = new Place(cursor.getInt(0), cursor.getString(1), cursor.getDouble(2), cursor.getDouble(3), cursor.getInt(4));
                    places.add(place);
                    return place.getLocationId();
                }
            } while (cursor.moveToNext());
        }


        return 0;
    }

    public void deleteSession(String id)
    {
        SQLiteDatabase db = this.getWritableDatabase();

        String deleteLocationQuery = "DELETE FROM place_data where placeId_fk= '" + id + "'";

        db.execSQL(deleteLocationQuery);
    }

    public ArrayList<Session> getSessions()
    {

        ArrayList<Session> locations = new ArrayList<>();
        SQLiteDatabase db = this.getWritableDatabase();

        String getLocationsQuery = "SELECT * FROM session";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);

        if (cursor.moveToFirst())
        {
            do
            {
                {
                    SimpleDateFormat session_date = new SimpleDateFormat("YYYY-mm-DD", Locale.getDefault());
                    try
                    {
                        Date date = session_date.parse(cursor.getString(0));

                        Session session = new Session(cursor.getLong(0), cursor.getLong(1), cursor.getLong(2), cursor.getLong(3), cursor.getLong(4), cursor.getInt(5));

                        locations.add(session);
                    } catch (ParseException e)
                    {
                        e.printStackTrace();
                    }

                }
            } while (cursor.moveToNext());
        }

        return locations;

    }

    public Place_Data getSession(String id)
    {

        SQLiteDatabase db = this.getWritableDatabase();

        String getLocationsQuery = "SELECT * FROM session Where placeId_fk = '" + id + "'";

        Cursor cursor = db.rawQuery(getLocationsQuery, null);

        if (cursor.moveToFirst())
        {
            do
            {
                {
                    SimpleDateFormat session_date = new SimpleDateFormat("YYYY-mm-DD", Locale.getDefault());
                    try
                    {
                        Date date = session_date.parse(cursor.getString(0));

                        Place_Data place_data = new Place_Data(date, cursor.getString(1), cursor.getString(2), cursor.getString(3),
                                cursor.getInt(4));

                        return place_data;

                    } catch (ParseException e)
                    {
                        e.printStackTrace();
                    }

                }
            } while (cursor.moveToNext());
        }

        return null;

    }


}
